package cn.xzq.cstm.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.xzq.cstm.domain.Customer;
import cn.xzq.cstm.domain.PageBean;
import cn.xzq.utils.TxQueryTunner;

/**
 * 持久化层
 * @author XC947
 *
 */
public class CustomerDao {
	//通过QueryRunner对数据库进行操作
	private QueryRunner qr = new TxQueryTunner();
	
	/**
	 * 添加客户
	 * @param customer
	 */
	public void add(Customer customer){
		try {
			String sql = "insert into t_customer values(?,?,?,?,?,?,?)";
			Object[] params = {
					customer.getCid(),
					customer.getCname(),
					customer.getGender(),
					customer.getBirthday(),
					customer.getCellphone(),
					customer.getEmail(),
					customer.getDescription()
			};
			qr.update(sql, params);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 查询所有客户
	 * @param customer
	 * @return List<Customer>
	 */
	public List<Customer> findAll(){
		try {
			String sql = "select * from t_customer";
			return qr.query(sql, new BeanListHandler<Customer>(Customer.class));
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	/**
	 * 查询所有客户-分页
	 * @param customer
	 * @return List<Customer>
	 */
	public PageBean<Customer> findAll(int currPageCode, int pageSize){
		/*
		 * 1、创建PageBean对象
		 * 2、设置currPageCode、pageSize给pageBean
		 * 3、得到totalRecord，设置给pageBean
		 * 4、得到datas，设置给PageBean
		 * 5、返回PageBean
		 */
		try {
			PageBean pageBean = new PageBean();
			pageBean.setPageSize(pageSize);
			pageBean.setCurrPageCode(currPageCode);
			pageBean.getTotalPage();
			//获取totalRecord数据
			String sqlc = "select count(*) from t_customer";
			Number num = (Number)qr.query(sqlc, new ScalarHandler());
			int totalRecord = num.intValue();
			System.out.println(totalRecord);
			pageBean.setTotalRecord(totalRecord);
			//得到datas
			String sqld = "select * from t_customer limit ?,?";
			Object[] params = {(currPageCode-1)*pageSize, pageSize};
			List<Customer> datas = qr.query(sqld, new BeanListHandler<Customer>(Customer.class), params);
			pageBean.setDatas(datas);
			
			return pageBean;
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 根据id查找一个客户
	 * @param customer
	 * @return Customer
	 */
	public Customer findById(String cid){
		try {
			String sql = "select * from t_customer where cid=?";
			Object[] params = {cid};
			return qr.query(sql, new BeanHandler<Customer>(Customer.class), params);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	
	/**
	 * 更新客户信息
	 * @param customer
	 */
	public void update(Customer customer){
		try {
			String sql = "update t_customer set "
					+ "cname=?,gender=?,birthday=?,cellphone=?,email=?,description=? "
					+ "where cid=?";
			Object[] params = {
					customer.getCname(),
					customer.getGender(),
					customer.getBirthday(),
					customer.getCellphone(),
					customer.getEmail(),
					customer.getDescription(),
					customer.getCid()
			};
			qr.update(sql, params);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 根据id删除客户
	 * @param cid
	 */
	public void deleteById(String cid){
		try {
			String sql = "delete from t_customer where cid=?";
			Object[] params = {cid};
			qr.update(sql, params);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 多条件查询客户
	 * @param customer
	 * @return List<Customer>
	 */
	/*public List<Customer> query(Customer customer){
		try {
			StringBuilder sql = new StringBuilder("select * from t_customer where 1=1 ");
			String cname = customer.getCname();
			String gender = customer.getGender();
			String cellphone = customer.getCellphone();
			String email = customer.getEmail();
			
			ArrayList<Object> params = new ArrayList<Object>();
			
			if(cname != null && !cname.isEmpty()){
				sql.append("and cname like ? ");
				params.add(cname);
			}
			if(gender != null && !gender.isEmpty()){
				sql.append("and gender like ? ");
				params.add(gender);
			}
			if(cellphone != null && !cellphone.isEmpty()){
				sql.append("and cellphone like ? ");
				params.add(cellphone);
			}
			if(email != null && !email.isEmpty()){
				sql.append("and email like ?");
				params.add(email);
			}
			//System.out.println(params);
			return qr.query(sql.toString(), new BeanListHandler<Customer>(Customer.class), params.toArray());
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}*/
	/**
	 * 多条件查询客户-分页
	 * @param customer
	 * @return List<Customer>
	 */
	public PageBean<Customer> query(Customer customer, int currPageCode, int pageSize){
		try {
			//定义查询语句
			StringBuilder cntSql = new StringBuilder("select * from t_customer ");
			StringBuilder whereSql = new StringBuilder(" where 1=1 ");
			//参数初始化
			PageBean pageBean = new PageBean();
			pageBean.setPageSize(pageSize);
			pageBean.setCurrPageCode(currPageCode);
			
			String cname = customer.getCname();
			String gender = customer.getGender();
			String cellphone = customer.getCellphone();
			String email = customer.getEmail();

			//参数列表
			ArrayList<Object> params = new ArrayList<Object>();
			if(cname != null && !cname.isEmpty()){
				whereSql.append("and cname like ? ");
				params.add("%" + cname + "%");
			}
			if(gender != null && !gender.isEmpty()){
				whereSql.append("and gender=? ");
				params.add(gender);
			}
			if(cellphone != null && !cellphone.isEmpty()){
				whereSql.append("and cellphone like ? ");
				params.add("%" + cellphone + "%");
			}
			if(email != null && !email.isEmpty()){
				whereSql.append("and email like ?");
				params.add( "%" + email + "%");
			}
			//获取totalRecord数据
			StringBuilder countSql = new StringBuilder("select count(*) from t_customer ");
			String sqlc = countSql.append(whereSql).toString();
			System.out.println(sqlc);
			Number num = (Number)qr.query(sqlc, new ScalarHandler(), params.toArray());
			int totalRecord = num.intValue();
			System.out.println(totalRecord);
			pageBean.setTotalRecord(totalRecord);
			System.out.println(pageBean);
			
			//得到datas,sql语句
			whereSql.append(" limit ?,?");
			String sqld = cntSql.append(whereSql).toString();
			
			params.add((currPageCode-1) * pageSize);
			params.add(pageSize); 
			List<Customer> datas = qr.query(sqld, new BeanListHandler<Customer>(Customer.class), params.toArray());
			pageBean.setDatas(datas);
			
			return pageBean;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
}
